﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OnlineAuction.Common;
using OnlineAuction.Entity;

namespace OnlineAuction.DataAccess
{
    public class CategoryDA
    {
        public DataSet GetCategory()
        {
            SqlConnection conn;
            Database db = new Database();
            //Get connection to database
            using (conn = db.GetConnection())
            {
                //Get Category from database
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter("GetCategory", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.Fill(ds);
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet ViewCategorys()
        {
            SqlConnection conn;
            Database db = new Database();
            //Get connection to database
            using (conn = db.GetConnection())
            {
                //Get Category from database
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter("viewCategory", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.Fill(ds);
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet GetCategoryForID(int CategoryID)
        {
            SqlConnection conn;
            Database db = new Database();
            //Get connection to database
            using (conn = db.GetConnection())
            {
                //Get Category from database
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlCommand command = new SqlCommand("GetCategoryForID", conn);
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));
                    da.Fill(ds);
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool AddItems(CategoryE category)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Add category to database
                try
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand("AddCategory", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CategoryName", category.CategoryName));
                    command.Parameters.Add(new SqlParameter("@CategoryLogo", category.CategoryLogo));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool UpdateCategory(CategoryE category)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Add category to database
                try
                {
                    int state = 0;
                    conn.Open();
                    SqlCommand command = new SqlCommand("updateCategory", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    if (category.CategoryState == true)
                    {
                        state = 1;
                    }
                    else
                    {
                        state = 0;
                    }
                    command.Parameters.Add(new SqlParameter("@CategoryID", category.CategoryID));
                    command.Parameters.Add(new SqlParameter("@CategoryName", category.CategoryName));
                    command.Parameters.Add(new SqlParameter("@CategoryLogo", category.CategoryLogo));
                    command.Parameters.Add(new SqlParameter("@CategoryState", state));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool MergeCategory(CategoryE ce1, CategoryE ce2, string categoryName)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Merger category in database
                try
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand("MergeCategory", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CategoryID1", ce1.CategoryID));
                    command.Parameters.Add(new SqlParameter("@CategoryID2", ce2.CategoryID));
                    command.Parameters.Add(new SqlParameter("@CategoryName", categoryName));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();

                }
            }
        }

        public bool DelelteCategory(int CategoryID)
        {
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Merger category in database
                try
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand("deleteCategory", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }
    }
}
